package com.iplant.aps.serviceimpl.dao.wms;

import com.iplant.aps.service.mesenum.*;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.Pagination;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.aps.APSBOMItem;
import com.iplant.aps.service.po.aps.APSTaskStep;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.excel.MyExcelSheet;
import com.iplant.aps.service.po.oms.OMSOrder;
import com.iplant.aps.service.po.wms.*;
import com.iplant.aps.service.utils.Configuration;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.MyHelperServiceImpl;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import com.iplant.aps.serviceimpl.dao.aps.APSBOMItemDAO;
import com.iplant.aps.serviceimpl.dao.oms.OMSOrderDAO;
import com.iplant.aps.serviceimpl.utils.aps.APSConstans;
import com.iplant.aps.serviceimpl.utils.aps.APSUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.lang.reflect.Field;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class WMSPickDemandItemDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(WMSPickDemandItemDAO.class);

    private static WMSPickDemandItemDAO Instance = null;

    private WMSPickDemandItemDAO() {
        super();
    }

    public static WMSPickDemandItemDAO getInstance() {
        if (Instance == null)
            Instance = new WMSPickDemandItemDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, WMSPickDemandItem wWMSPickDemandItem, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wWMSPickDemandItem == null)
                return 0;

            String wSQL = "";
            if (wWMSPickDemandItem.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.wms_pickdemanditem(DemandID,MaterialID,MaterialNo,"
                                + "MaterialName,FQTY,PartID,WBSNo,PartPointID,PartPointCode,PartPointName,"
                                + "RowNo,GroupFlag,ReplaceType,ReplaceTypeText,OutSourceType,OutSourceTypeText,"
                                + "AssessmentType,KittingFlag,Status,LoadingTime,TaskStepID,MaterialType,BOMNumber,"
                                + "BatchNumber,Stock,Supplier,ProductName,SerialNo,ProductTime,BOMItemNum," +
                                "PickedNumber," +
                                "IsConfirmed,SorterID,Sorter,SorterNo,StockPosition,ToHandFQTY,DeleteFlag,UpdateFlag," +
                                "UpdateErrorMsg,OutSourceMaterialID,IsOriDis) "
                                + "VALUES(:DemandID,:MaterialID,:MaterialNo,:MaterialName,:FQTY,:PartID,:WBSNo," +
                                ":PartPointID,"
                                + ":PartPointCode,:PartPointName,:RowNo,:GroupFlag,:ReplaceType,:ReplaceTypeText,"
                                + ":OutSourceType,:OutSourceTypeText,:AssessmentType,:KittingFlag,"
                                + ":Status,:LoadingTime,:TaskStepID,:MaterialType,:BOMNumber,:BatchNumber,:Stock,"
                                + ":Supplier,:ProductName,:SerialNo,:ProductTime,:BOMItemNum,:PickedNumber," +
                                ":IsConfirmed,:SorterID,:Sorter,:SorterNo,:StockPosition,:ToHandFQTY,:DeleteFlag," +
                                ":UpdateFlag,:UpdateErrorMsg,:OutSourceMaterialID,:IsOriDis);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat
                        .format("UPDATE {0}.wms_pickdemanditem SET DemandID = :DemandID,MaterialID = :MaterialID,"
                                + "MaterialNo = :MaterialNo,MaterialName = :MaterialName,FQTY = :FQTY,WBSNo = :WBSNo," +
                                "PartID=:PartID,"
                                + "PartPointID = :PartPointID,PartPointCode = :PartPointCode,PartPointName = " +
                                ":PartPointName,"
                                + "RowNo = :RowNo,GroupFlag = :GroupFlag,ReplaceType = :ReplaceType,ReplaceTypeText =" +
                                " :ReplaceTypeText,"
                                + "OutSourceType = :OutSourceType,OutSourceTypeText = :OutSourceTypeText,"
                                + "AssessmentType=:AssessmentType,KittingFlag=:KittingFlag,Status=:Status,"
                                + "LoadingTime=:LoadingTime,TaskStepID=:TaskStepID,PartID=:PartID,"
                                + "MaterialType=:MaterialType,BOMNumber=:BOMNumber,BatchNumber=:BatchNumber,"
                                + "Stock=:Stock,Supplier=:Supplier,ProductName=:ProductName,SerialNo=:SerialNo,"
                                + "ProductTime=:ProductTime,BOMItemNum=:BOMItemNum,PickedNumber=:PickedNumber," +
                                "IsConfirmed=:IsConfirmed,SorterID=:SorterID,Sorter=:Sorter,SorterNo=:SorterNo," +
                                "StockPosition=:StockPosition,ToHandFQTY=:ToHandFQTY,DeleteFlag=:DeleteFlag," +
                                "UpdateFlag=:UpdateFlag,UpdateErrorMsg=:UpdateErrorMsg," +
                                "OutSourceMaterialID=:OutSourceMaterialID,IsOriDis=:IsOriDis WHERE " +
                                "ID = :ID;", wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wWMSPickDemandItem.ID);
            wParamMap.put("DemandID", wWMSPickDemandItem.DemandID);
            wParamMap.put("MaterialID", wWMSPickDemandItem.MaterialID);
            wParamMap.put("MaterialNo", wWMSPickDemandItem.MaterialNo);
            wParamMap.put("MaterialName", wWMSPickDemandItem.MaterialName);
            wParamMap.put("FQTY", wWMSPickDemandItem.FQTY);
            wParamMap.put("WBSNo", wWMSPickDemandItem.WBSNo);
            wParamMap.put("PartID", wWMSPickDemandItem.PartID);
            wParamMap.put("PartPointID", wWMSPickDemandItem.PartPointID);
            wParamMap.put("PartPointCode", wWMSPickDemandItem.PartPointCode);
            wParamMap.put("PartPointName", wWMSPickDemandItem.PartPointName);
            wParamMap.put("RowNo", wWMSPickDemandItem.RowNo);
            wParamMap.put("GroupFlag", wWMSPickDemandItem.GroupFlag);
            wParamMap.put("ReplaceType", wWMSPickDemandItem.ReplaceType);
            wParamMap.put("ReplaceTypeText", wWMSPickDemandItem.ReplaceTypeText);
            wParamMap.put("OutSourceType", wWMSPickDemandItem.OutSourceType);
            wParamMap.put("OutSourceTypeText", wWMSPickDemandItem.OutSourceTypeText);
            wParamMap.put("AssessmentType", wWMSPickDemandItem.AssessmentType);
            wParamMap.put("KittingFlag", wWMSPickDemandItem.KittingFlag);
            wParamMap.put("Status", wWMSPickDemandItem.Status);
            wParamMap.put("LoadingTime", wWMSPickDemandItem.LoadingTime);
            wParamMap.put("TaskStepID", wWMSPickDemandItem.TaskStepID);
            wParamMap.put("MaterialType", wWMSPickDemandItem.MaterialType);
            wParamMap.put("BOMNumber", wWMSPickDemandItem.BOMNumber);
            wParamMap.put("BatchNumber", wWMSPickDemandItem.BatchNumber);
            wParamMap.put("Stock", wWMSPickDemandItem.Stock);
            wParamMap.put("Supplier", wWMSPickDemandItem.Supplier);
            wParamMap.put("ProductName", wWMSPickDemandItem.ProductName);
            wParamMap.put("SerialNo", wWMSPickDemandItem.SerialNo);
            wParamMap.put("ProductTime", wWMSPickDemandItem.ProductTime);
            wParamMap.put("BOMItemNum", wWMSPickDemandItem.BOMItemNum);
            wParamMap.put("PickedNumber", wWMSPickDemandItem.PickedNumber);
            wParamMap.put("IsConfirmed", wWMSPickDemandItem.IsConfirmed);

            wParamMap.put("SorterID", wWMSPickDemandItem.SorterID);
            wParamMap.put("Sorter", wWMSPickDemandItem.Sorter);
            wParamMap.put("SorterNo", wWMSPickDemandItem.SorterNo);
            wParamMap.put("StockPosition", wWMSPickDemandItem.StockPosition);
            wParamMap.put("ToHandFQTY", wWMSPickDemandItem.ToHandFQTY);

            wParamMap.put("DeleteFlag", wWMSPickDemandItem.DeleteFlag);
            wParamMap.put("UpdateFlag", wWMSPickDemandItem.UpdateFlag);
            wParamMap.put("UpdateErrorMsg", wWMSPickDemandItem.UpdateErrorMsg);
            wParamMap.put("OutSourceMaterialID", wWMSPickDemandItem.OutSourceMaterialID);
            wParamMap.put("IsOriDis", wWMSPickDemandItem.IsOriDis);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wWMSPickDemandItem.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wWMSPickDemandItem.setID(wResult);
            } else {
                wResult = wWMSPickDemandItem.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<WMSPickDemandItem> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (WMSPickDemandItem wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.wms_pickdemanditem WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public WMSPickDemandItem SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        WMSPickDemandItem wResult = new WMSPickDemandItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<WMSPickDemandItem> wList = SelectList(wLoginUser, wID, -1, -1, "", -1, wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
            if (wResult.DemandID > 0) {
                wResult.DemandNo = WMSPickDemandDAO.getInstance().SelectByID(wLoginUser, wResult.DemandID,
                        wErrorCode).DemandNo;
            }
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<WMSPickDemandItem> SelectList(BMSEmployee wLoginUser, int wID, int wDemandID, int wMaterialID,
                                              String wMaterialNo, int wPartPointID, OutResult<Integer> wErrorCode) {
        List<WMSPickDemandItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t2.Name UnitText,t3.Code PartCode,t1.MaterialName DBMaterialName,t1.Recoil FROM {0}" +
                            ".wms_pickdemanditem t,{0}.mss_material t1," +
                            "{0}.cfg_unit t2,{0}.fpc_part t3 "
                            + "WHERE t.MaterialID=t1.ID and t1.CYUnitID=t2.ID and t.PartID=t3.ID "
                            + "and ( :wID <= 0 or :wID = t.ID ) "
                            + "and ( :wDemandID <= 0 or :wDemandID = t.DemandID ) "
                            + "and ( :wMaterialID <= 0 or :wMaterialID = t.MaterialID ) "
                            + "and ( :wMaterialNo is null or :wMaterialNo = '''' or :wMaterialNo = t.MaterialNo ) "
                            + "and ( :wPartPointID <= 0 or :wPartPointID = t.PartPointID );",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wDemandID", wDemandID);
            wParamMap.put("wMaterialID", wMaterialID);
            wParamMap.put("wMaterialNo", wMaterialNo);
            wParamMap.put("wPartPointID", wPartPointID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);
            SetValue(wResultList, wQueryResult, wSDF, wBaseTime1);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 条件查询集合
     */
    public List<WMSPickDemandItem> SelectListByDemandIDList(BMSEmployee wLoginUser, List<Integer> wDemandIDList,
                                                            OutResult<Integer> wErrorCode) {
        List<WMSPickDemandItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (wDemandIDList == null || wDemandIDList.size() <= 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t2.Name UnitText,t3.Code PartCode,t1.MaterialName DBMaterialName,t1.Recoil FROM {0}" +
                            ".wms_pickdemanditem t,{0}.mss_material t1," +
                            "{0}.cfg_unit t2,{0}.fpc_part t3 "
                            + "WHERE t.MaterialID=t1.ID and t1.CYUnitID=t2.ID and t.PartID=t3.ID "
                            + "and ( :wDemandID is null or :wDemandID = '''' or t.DemandID in ({1}));",
                    wInstance.Result,
                    StringUtils.Join(",", wDemandIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wDemandID", StringUtils.Join(",", wDemandIDList));

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);
            SetValue(wResultList, wQueryResult, wSDF, wBaseTime1);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    private void SetValue(List<WMSPickDemandItem> wResultList, List<Map<String, Object>> wQueryResult,
                          SimpleDateFormat wSDF, Calendar wBaseTime1) {
        try {
            boolean wIsActiveRowHandOver = Configuration.readConfigString("IsActiveRowHandOver", "config/config")
                    .equals("1");

            for (Map<String, Object> wReader : wQueryResult) {
                WMSPickDemandItem wItem = new WMSPickDemandItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.DemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("DBMaterialName"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.FQTY = Math.round(wItem.FQTY * 100) * 0.01;

                wItem.BOMNumber = StringUtils.parseDouble(wReader.get("BOMNumber"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartCode = StringUtils.parseString(wReader.get("PartCode"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.PartPointCode = StringUtils.parseString(wReader.get("PartPointCode"));
                wItem.PartPointName = StringUtils.parseString(wReader.get("PartPointName"));
                wItem.RowNo = StringUtils.parseString(wReader.get("RowNo"));
                wItem.GroupFlag = StringUtils.parseString(wReader.get("GroupFlag"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceTypeText = StringUtils.parseString(wReader.get("ReplaceTypeText"));
                wItem.OutSourceType = StringUtils.parseInt(wReader.get("OutSourceType"));
                wItem.OutSourceTypeText = StringUtils.parseString(wReader.get("OutSourceTypeText"));
                wItem.AssessmentType = StringUtils.parseString(wReader.get("AssessmentType"));
                wItem.KittingFlag = StringUtils.parseString(wReader.get("KittingFlag"));
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.TaskStepID = StringUtils.parseInt(wReader.get("TaskStepID"));
                wItem.MaterialType = StringUtils.parseInt(wReader.get("MaterialType"));
                // 反冲标记查询
                int wRecoil = StringUtils.parseInt(wReader.get("Recoil"));
                if (wRecoil == 1) {
                    wItem.MaterialType = 2;
                } else {
                    wItem.MaterialType = 1;
                }
                wItem.BOMItemNum = StringUtils.parseInt(wReader.get("BOMItemNum"));
                wItem.UnitText = StringUtils.parseString(wReader.get("UnitText"));
                wItem.PickedNumber = StringUtils.parseDouble(wReader.get("PickedNumber"));
                wItem.IsConfirmed = StringUtils.parseInt(wReader.get("IsConfirmed"));

                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.Stock = StringUtils.parseString(wReader.get("Stock"));
                wItem.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wItem.ProductName = StringUtils.parseString(wReader.get("ProductName"));
                wItem.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wItem.ProductTime = StringUtils.parseCalendar(wReader.get("ProductTime"));

                wItem.SorterID = StringUtils.parseInt(wReader.get("SorterID"));
                wItem.Sorter = StringUtils.parseString(wReader.get("Sorter"));
                wItem.SorterNo = StringUtils.parseString(wReader.get("SorterNo"));
                wItem.StockPosition = StringUtils.parseString(wReader.get("StockPosition"));
                wItem.ToHandFQTY = StringUtils.parseDouble(wReader.get("ToHandFQTY"));

                wItem.DeleteFlag = StringUtils.parseInt(wReader.get("DeleteFlag"));
                wItem.UpdateFlag = StringUtils.parseInt(wReader.get("UpdateFlag"));
                wItem.UpdateErrorMsg = StringUtils.parseString(wReader.get("UpdateErrorMsg"));
                wItem.OutSourceMaterialID = StringUtils.parseInt(wReader.get("OutSourceMaterialID"));

                wItem.MaterialTypeText = Objects.requireNonNull(MSSMaterialType.getEnumType(wItem.MaterialType))
                        .getLable();
                wItem.StatusText = WMSPickDemandItemStatus.getEnumType(wItem.Status).getLable();
                if (wIsActiveRowHandOver) {
                    wItem.StatusText = WMSPickDemandItemStatusNew.getEnumType(wItem.Status).getLable();
                }
                wItem.LoadingTime = StringUtils.parseCalendar(wReader.get("LoadingTime"));
                wItem.LoadingTimeText = wItem.LoadingTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.LoadingTime.getTime())
                        : "";
                wItem.IsOriDis = StringUtils.parseString(wReader.get("IsOriDis"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public List<Integer> SelectDemandList(BMSEmployee wLoginUser, String wMaterial, Calendar wStartTime,
                                          Calendar wEndTime, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            // String wSQL = StringUtils.Format("SELECT distinct DemandID FROM
            // {0}.wms_pickdemanditem where "
            // + "MaterialNo like ''%{1}%'' or MaterialName like ''%{1}%'';",
            // wInstance.Result, wMaterial);

            String wSQL = StringUtils.Format("SELECT DISTINCT DemandID FROM {0}.wms_pickdemanditem t " +
                    "LEFT JOIN {0}.wms_pickdemand t1 ON t.DemandID=t1.ID " +
                    "WHERE (t.MaterialNo LIKE ''%{1}%'' OR t.MaterialName LIKE ''%{1}%'') " +
                    "AND t1.CreateTime>=:wStartTime " +
                    "AND t1.CreateTime<=:wEndTime;", wInstance.Result, wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wDemandID = StringUtils.parseInt(wReader.get("DemandID"));
                if (wDemandID > 0 && wResult.stream().noneMatch(p -> p == wDemandID))
                    wResult.add(wDemandID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 根据工序任务更新配送明细工序任务ID
     *
     * @param wLoginUser   登录信息
     * @param wAPSTaskStep 工序任务
     * @param wErrorCode   错误信息
     */
    public void UpdateTaskStepID(BMSEmployee wLoginUser, APSTaskStep wAPSTaskStep, OutResult<Integer> wErrorCode) {
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return;
            }

            String wSQL = StringUtils.Format(
                    "update {0}.wms_pickdemanditem set TaskStepID=:TaskStepID where "
                            + "WBSNo=:WBSNo and PartID=:PartID and PartPointID=:PartPointID and ID>0;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("TaskStepID", wAPSTaskStep.ID);
            wParamMap.put("WBSNo", wAPSTaskStep.OrderNo);
            wParamMap.put("PartID", wAPSTaskStep.PartID);
            wParamMap.put("PartPointID", wAPSTaskStep.StepID);

            wSQL = this.DMLChange(wSQL);

            shrisJdbcTemplate.update(wSQL, wParamMap);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 查询在线物料
     *
     * @param wLoginUser    登录信息
     * @param wProductID    车型ID
     * @param wLineID       修程ID
     * @param wCustomerID   局段ID
     * @param wOrderID      订单ID
     * @param wPartID       工位ID
     * @param wStepID       工序ID
     * @param wMaterial     物料
     * @param wStatus       装车状态
     * @param wMaterialType 物料类型
     * @param wErrorCode    错误信息
     * @return 在线物料明细
     */

    public List<WMSPickDemandItem> WMS_QueryOnlineMaterial(BMSEmployee wLoginUser, int wProductID, int wLineID,
                                                           int wCustomerID, int wOrderID, int wPartID, int wStepID,
                                                           String wMaterial, int wStatus, int wMaterialType,
                                                           OutResult<Integer> wErrorCode) {
        List<WMSPickDemandItem> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT t1.*,t2.ProductID,t2.LineID,t2.CustomerID,t2.PartNo,t2.ReceiveTime FROM {0}" +
                            ".wms_pickdemanditem t1,iplantmes.wms_pickdemand t2 where t1.DemandID=t2.ID  "
                            + "and ( :wProductID <= 0 or :wProductID = t2.ProductID ) "
                            + "and ( :wLineID <= 0 or :wLineID = t2.LineID ) "
                            + "and ( :wCustomerID <= 0 or :wCustomerID = t2.CustomerID ) "
                            + "and ( :wOrderID <= 0 or :wOrderID = t2.OrderID ) "
                            + "and ( :wPartID <= 0 or :wPartID = t2.PartID ) "
                            + "and ( :wPartPointID <= 0 or :wPartPointID = t1.PartPointID ) "
                            + "and ( :wStatus <= 0 or :wStatus = t1.Status ) " + "and ( t1.Status > 0 ) "
                            + "and ( t1.Status != 4 ) " + "and ( t2.Status = 5 ) "
                            + "and ( :wMaterialType <= 0 or :wMaterialType = t1.MaterialType ) "
                            + "and ( :wMaterial is null or :wMaterial = '''' or (t1.MaterialName like ''%{1}%'' or t1" +
                            ".MaterialNo like ''%%'')  );",
                    wInstance.Result, wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wPartPointID", wStepID);
            wParamMap.put("wMaterial", wMaterial);
            wParamMap.put("wStatus", wStatus);
            wParamMap.put("wMaterialType", wMaterialType);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat wSDF1 = new SimpleDateFormat("yyyy-MM-dd");
            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);
            for (Map<String, Object> wReader : wQueryResult) {
                WMSPickDemandItem wItem = new WMSPickDemandItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.DemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.PartPointCode = StringUtils.parseString(wReader.get("PartPointCode"));
                wItem.PartPointName = StringUtils.parseString(wReader.get("PartPointName"));
                wItem.RowNo = StringUtils.parseString(wReader.get("RowNo"));
                wItem.GroupFlag = StringUtils.parseString(wReader.get("GroupFlag"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceTypeText = StringUtils.parseString(wReader.get("ReplaceTypeText"));
                wItem.OutSourceType = StringUtils.parseInt(wReader.get("OutSourceType"));
                wItem.OutSourceTypeText = StringUtils.parseString(wReader.get("OutSourceTypeText"));
                wItem.AssessmentType = StringUtils.parseString(wReader.get("AssessmentType"));
                wItem.KittingFlag = StringUtils.parseString(wReader.get("KittingFlag"));
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.TaskStepID = StringUtils.parseInt(wReader.get("TaskStepID"));
                wItem.MaterialType = StringUtils.parseInt(wReader.get("MaterialType"));
                wItem.BOMItemNum = StringUtils.parseInt(wReader.get("BOMItemNum"));
                wItem.PickedNumber = StringUtils.parseInt(wReader.get("PickedNumber"));
                wItem.IsConfirmed = StringUtils.parseInt(wReader.get("IsConfirmed"));

                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.Stock = StringUtils.parseString(wReader.get("Stock"));
                wItem.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wItem.ProductName = StringUtils.parseString(wReader.get("ProductName"));
                wItem.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wItem.ProductTime = StringUtils.parseCalendar(wReader.get("ProductTime"));

                wItem.SorterID = StringUtils.parseInt(wReader.get("SorterID"));
                wItem.Sorter = StringUtils.parseString(wReader.get("Sorter"));
                wItem.SorterNo = StringUtils.parseString(wReader.get("SorterNo"));
                wItem.StockPosition = StringUtils.parseString(wReader.get("StockPosition"));
                wItem.ToHandFQTY = StringUtils.parseDouble(wReader.get("ToHandFQTY"));

                wItem.DeleteFlag = StringUtils.parseInt(wReader.get("DeleteFlag"));
                wItem.UpdateFlag = StringUtils.parseInt(wReader.get("UpdateFlag"));
                wItem.UpdateErrorMsg = StringUtils.parseString(wReader.get("UpdateErrorMsg"));
                wItem.OutSourceMaterialID = StringUtils.parseInt(wReader.get("OutSourceMaterialID"));

                wItem.MaterialTypeText = Objects.requireNonNull(MSSMaterialType.getEnumType(wItem.MaterialType))
                        .getLable();
                wItem.StatusText = WMSPickDemandItemStatus.getEnumType(wItem.Status).getLable();
                wItem.LoadingTime = StringUtils.parseCalendar(wReader.get("LoadingTime"));
                wItem.LoadingTimeText = wItem.LoadingTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.LoadingTime.getTime())
                        : "";
                wItem.ReceiveTime = StringUtils.parseCalendar(wReader.get("ReceiveTime"));
                wItem.ReceiveTimeText = wItem.ReceiveTime.compareTo(wBaseTime1) > 0
                        ? wSDF1.format(wItem.ReceiveTime.getTime())
                        : "";

                wItem.ProductNo = APSConstans.GetFPCProduct(StringUtils.parseInt(wReader.get("ProductID"))).ProductNo;
                wItem.LineName = APSConstans.GetFMCLineName(StringUtils.parseInt(wReader.get("LineID")));
                wItem.CustomerName = APSConstans.GetCRMCustomerName(StringUtils.parseInt(wReader.get("CustomerID")));
                wItem.PartName = APSConstans.GetFPCPartName(wItem.PartID);
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));

                wResult.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取在途库存
     *
     * @param wLoginUser  登录信息
     * @param wMaterialID 物料ID
     * @param wErrorCode  错误信息
     * @return 在途库存
     */
    public double GetFQTYByMaterialID(BMSEmployee wLoginUser, int wMaterialID, OutResult<Integer> wErrorCode) {
        double wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT t1.FQTY FROM {0}.wms_pickdemanditem t1,{0}.wms_pickdemand t2 "
                            + "where t1.DemandID=t2.ID and t1.MaterialID=:MaterialID and t1.MaterialType=2 " +
                            "and t2.Status in (2,3,4);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("MaterialID", wMaterialID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                double wFQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wResult += wFQTY;
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public WMSPickDemandItem SelectByCondition(BMSEmployee wLoginUser, int wOrderID, int wPartID, int wPartPointID,
                                               int wMaterialID, OutResult<Integer> wErrorCode) {
        WMSPickDemandItem wResult = new WMSPickDemandItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t.* FROM {0}" +
                            ".WMS_PickDemandItem t,{0}.WMS_PickDemand t1 WHERE t.DemandID=t1.ID AND t1" +
                            ".OrderID=:OrderID AND t1.SourceType=1 AND t.MaterialID=:MaterialID AND t.PartID=:PartID " +
                            "AND t" +
                            ".PartPointID=:PartPointID and t.Status in (1,2,6) and t1.Status=4;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("OrderID", wOrderID);
            wParamMap.put("MaterialID", wMaterialID);
            wParamMap.put("PartID", wPartID);
            wParamMap.put("PartPointID", wPartPointID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.ID = StringUtils.parseInt(wReader.get("ID"));
                wResult.DemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wResult.OrderID = StringUtils.parseInt(wReader.get("Stock"));
                wResult.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wResult.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wResult.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wResult.AssessmentType = StringUtils.parseString(wReader.get("AssessmentType"));
                wResult.PartPointCode = StringUtils.parseString(wReader.get("PartPointCode"));
                wResult.PartPointName = StringUtils.parseString(wReader.get("PartPointName"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取导出数据
     */
    public MyExcelSheet GetMyExcelSheet(int wDemandID) {
        MyExcelSheet wResult = new MyExcelSheet();
        try {
            OutResult<Integer> wErrorCode = new OutResult<>(0);

            WMSPickDemand wWMSPickDemand = WMSPickDemandDAO.getInstance().SelectByID(BaseDAO.SysAdmin, wDemandID,
                    wErrorCode);

            wResult.SheetName = "配送单明细";
            wResult.TitleName = "配送单明细";
            wResult.HeaderList = new ArrayList<>(Arrays.asList("行号", "需求单号", "车型", "车号", "工位", "工序", "物料编码", "物料名称",
                    "数量", "已拣", "分拣员", "库存地点", "供应商", "生产厂家", "序列号/铁总唯一码", "生产日期", "批次号", "必换偶换", "必修偶修", "评估类型",
                    "配套标记", "状态"));
            wResult.DataList = new ArrayList<>();

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd");

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2010, 0, 1, 0, 0, 0);
            // 排序
            wWMSPickDemand.ItemList.sort(Comparator.comparing(WMSPickDemandItem::getMaterialNo));
            int wIndex = 1;
            for (WMSPickDemandItem wWMSPickDemandItem : wWMSPickDemand.ItemList) {
                List<String> wValueList = new ArrayList<>(Arrays.asList(String.valueOf(wIndex),
                        wWMSPickDemand.DemandNo, wWMSPickDemand.ProductNo, wWMSPickDemand.PartNo.split("#")[1],
                        wWMSPickDemand.PartName, wWMSPickDemandItem.PartPointName, wWMSPickDemandItem.MaterialNo,
                        wWMSPickDemandItem.MaterialName,
                        String.valueOf(wWMSPickDemandItem.FQTY), String.valueOf(wWMSPickDemandItem.PickedNumber),
                        wWMSPickDemandItem.Sorter, wWMSPickDemandItem.Stock, wWMSPickDemandItem.Supplier,
                        wWMSPickDemandItem.ProductName, wWMSPickDemandItem.SerialNo,
                        wWMSPickDemandItem.ProductTime.compareTo(wBaseTime) > 0
                                ? wSDF.format(wWMSPickDemandItem.ProductTime.getTime())
                                : "",
                        wWMSPickDemandItem.BatchNumber, wWMSPickDemandItem.ReplaceTypeText,
                        wWMSPickDemandItem.OutSourceTypeText, wWMSPickDemandItem.AssessmentType,
                        wWMSPickDemandItem.KittingFlag,
                        wWMSPickDemandItem.StatusText));
                wResult.DataList.add(wValueList);
                wIndex++;
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public MyExcelSheet GetMyExcelSheet(String wDemandIDs) {
        MyExcelSheet wResult = new MyExcelSheet();
        try {
            if (StringUtils.isEmpty(wDemandIDs))
                return wResult;

            List<Integer> wDemandIDList = StringUtils.parseIntList(wDemandIDs.split(","));
            if (wDemandIDList == null || wDemandIDList.size() <= 0)
                return wResult;

            OutResult<Integer> wErrorCode = new OutResult<>(0);

            wResult.SheetName = "配送单明细";
            wResult.TitleName = "配送单明细";
            wResult.HeaderList = new ArrayList<>(Arrays.asList("行号", "需求单号", "车型", "车号", "工位", "工序", "物料编码", "物料名称",
                    "数量", "已拣", "分拣员", "库存地点", "供应商", "生产厂家", "序列号/铁总唯一码", "生产日期", "批次号", "必换偶换", "必修偶修", "评估类型",
                    "配套标记", "状态"));
            wResult.DataList = new ArrayList<>();

            List<WMSPickDemandItem> wItemList = this.WMS_QueryExportItemList(BaseDAO.SysAdmin, wDemandIDList,
                    wErrorCode);
            if (wItemList == null || wItemList.size() <= 0)
                return wResult;

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd");

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2010, 0, 1, 0, 0, 0);
            int wIndex = 1;
            for (WMSPickDemandItem wWMSPickDemandItem : wItemList) {
                List<String> wValueList = new ArrayList<>(Arrays.asList(String.valueOf(wIndex),
                        wWMSPickDemandItem.DemandNo, wWMSPickDemandItem.ProductNo, wWMSPickDemandItem.PartNo.split(
                                "#")[1],
                        wWMSPickDemandItem.PartName, wWMSPickDemandItem.PartPointName, wWMSPickDemandItem.MaterialNo,
                        wWMSPickDemandItem.MaterialName,
                        String.valueOf(wWMSPickDemandItem.FQTY), String.valueOf(wWMSPickDemandItem.PickedNumber),
                        wWMSPickDemandItem.Sorter, wWMSPickDemandItem.Stock, wWMSPickDemandItem.Supplier,
                        wWMSPickDemandItem.ProductName, wWMSPickDemandItem.SerialNo,
                        wWMSPickDemandItem.ProductTime.compareTo(wBaseTime) > 0
                                ? wSDF.format(wWMSPickDemandItem.ProductTime.getTime())
                                : "",
                        wWMSPickDemandItem.BatchNumber, wWMSPickDemandItem.ReplaceTypeText,
                        wWMSPickDemandItem.OutSourceTypeText, wWMSPickDemandItem.AssessmentType,
                        wWMSPickDemandItem.KittingFlag,
                        wWMSPickDemandItem.StatusText));
                wResult.DataList.add(wValueList);
                wIndex++;
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    private List<WMSPickDemandItem> WMS_QueryExportItemList(BMSEmployee wLoginUser, List<Integer> wDemandIDList,
                                                            OutResult<Integer> wErrorCode) {
        List<WMSPickDemandItem> wResult = new ArrayList<>();
        try {
            if (wDemandIDList == null || wDemandIDList.size() <= 0)
                return wResult;

            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t2.DemandNo,t2.ProductNo,t2.PartNo,t2.PartName,t1.PartPointName," +
                            "t1.MaterialNo,t1.MaterialName,t1.FQTY,t1.PickedNumber,t1.Sorter," +
                            "t1.Stock,t1.Supplier,t1.ProductName,t1.SerialNo,t1.ProductTime,t1.BatchNumber,t1" +
                            ".ReplaceType,t1.OutSourceType,t1.AssessmentType,t1.GroupFlag,t1.Status " +
                            "FROM {0}.wms_pickdemanditem t1,{0}.wms_pickdemand t2 " +
                            "WHERE t1.DemandID=t2.ID AND t2.ID IN ({1}) ORDER BY t2.DemandNo ASC,t1.MaterialNo " +
                            "ASC;",
                    wInstance.Result, StringUtils.Join(",", wDemandIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);
            for (Map<String, Object> wReader : wQueryResult) {
                WMSPickDemandItem wWMSPickDemandItem = mapToPOJO(wReader, WMSPickDemandItem.class);
                wResult.add(wWMSPickDemandItem);
            }

            wResult.forEach(p -> {
                p.ReplaceTypeText = APSReplaceType.getEnumType(p.ReplaceType).getLable();
                p.OutSourceTypeText = APSOutSourceType.getEnumType(p.OutSourceType).getLable();
                p.StatusText = WMSPickDemandItemStatus.getEnumType(p.Status).getLable();
            });
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 例:map中有key为"id",value为"001"的数据,类对象为User,User中有Spring id;属性,
     * 返回值:返回User对象,你将获得一个id属性为"001"的User对象
     */
    public <T> T mapToPOJO(Map map, Class clazz) throws Exception {
        // 通过class对象拿到所有的字段(包括私有)
        Field[] declaredFields = clazz.getDeclaredFields();
        // 通过class对象拿到实例对象,虽然此时它是Object类型
        Object newEntity = clazz.newInstance();
        try {
            // 循环Field[]拿到每个属性对象
            for (Field field : declaredFields) {
                // 打开属性对象的赋值权限
                field.setAccessible(true);
                if (map.get(field.getName()) != null) {
                    String s = field.getType().toString();
                    if (s.equals("class java.util.Calendar")) {
                        Timestamp o = (Timestamp) map.get(field.getName());
                        Calendar calendar = Calendar.getInstance();
                        calendar.setTime(new Date(o.getTime()));
                        field.set(newEntity, calendar);
                    } else {
                        // 然后通过map.get(field.getName())取到map里面的值field.set()进实例对象
                        field.set(newEntity, map.get(field.getName()));
                    }
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        // 我们把Object转成泛型返回
        return (T) newEntity;
    }

    public int BatchConfirm(BMSEmployee wLoginUser, int partPointID, int materialID, String wPickDemandIDs,
                            OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("UPDATE {0}.wms_pickdemanditem SET IsConfirmed=1 WHERE " +
                            "DemandID IN ({1}) AND PartPointID=:PartPointID AND MaterialID=:MaterialID AND ID>0;",
                    wInstance.Result, wPickDemandIDs);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("PartPointID", partPointID);
            wParamMap.put("MaterialID", materialID);

            wSQL = this.DMLChange(wSQL);

            shrisJdbcTemplate.update(wSQL, wParamMap);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<WMSPickDemandItem> SelectListByDemandIDs(BMSEmployee wLoginUser, String wDemandIDs,
                                                         OutResult<Integer> wErrorCode) {
        List<WMSPickDemandItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t2.Name UnitText,t3.Code PartCode,t1.MaterialName DBMaterialName FROM {0}" +
                            ".wms_pickdemanditem t,{0}.mss_material t1," +
                            "{0}.cfg_unit t2,{0}.fpc_part t3 "
                            + "WHERE t.MaterialID=t1.ID and t1.CYUnitID=t2.ID and t.PartID=t3.ID "
                            + "and ( t.DemandID in ({1}));",
                    wInstance.Result, wDemandIDs);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);
            SetValue(wResultList, wQueryResult, wSDF, wBaseTime1);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 更新是否确认字段
     */
    public void UpdateIsConfirm(BMSEmployee wLoginUser, WMSPickDemandItem wWMSPickDemandItem,
                                OutResult<Integer> wErrorCode) {
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return;
            }

            String wSQL = StringUtils.Format("UPDATE {0}.wms_pickdemanditem SET IsConfirmed=:IsConfirmed WHERE ID=:ID;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("IsConfirmed", wWMSPickDemandItem.IsConfirmed);
            wParamMap.put("ID", wWMSPickDemandItem.ID);

            wSQL = this.DMLChange(wSQL);

            shrisJdbcTemplate.update(wSQL, wParamMap);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public List<WMSPickDemandItem> WMS_QueryPickDemandItemList(BMSEmployee wLoginUser, int wOrderID, int wPartID,
                                                               int wPartPointID, String wMaterial,
                                                               OutResult<Integer> wErrorCode) {
        List<WMSPickDemandItem> wResult = new ArrayList<WMSPickDemandItem>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t.*,t3.Name UnitText,t4.Code PartCode,t2.MaterialName " +
                            "DBMaterialName," +
                            "t1.PartNo,t1.ProductNo,t1.LineName,t1.CustomerName,t1.DemandNo,t4.Name PartName FROM {0}" +
                            ".`wms_pickdemanditem` t " +
                            "LEFT JOIN {0}.`wms_pickdemand` t1 ON t.DemandID=t1.ID "
                            + " left join {0}.mss_material t2 on t.MaterialID=t2.ID "
                            + " left join {0}.cfg_unit t3 on t2.CYUnitID=t3.ID "
                            + " left join {0}.fpc_part t4 on t.PartID=t4.ID "
                            + "WHERE t1.OrderID=:OrderID AND t.PartID=:PartID and t1.Status=4 " +
                            "AND (:wPartPointID<=0 or t.PartPointID=:wPartPointID )" +
                            "AND ( :wMaterial is null or :wMaterial='''' or  t.MaterialNo LIKE ''%{1}%'');",
                    wInstance.Result, wMaterial);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("OrderID", wOrderID);
            wParamMap.put("PartID", wPartID);
            wParamMap.put("wPartPointID", wPartPointID);
            wParamMap.put("wMaterial", wMaterial);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);

            for (Map<String, Object> wReader : wQueryResult) {
                WMSPickDemandItem wItem = new WMSPickDemandItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.DemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("DBMaterialName"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.FQTY = Math.round(wItem.FQTY * 100) * 0.01;

                wItem.BOMNumber = StringUtils.parseDouble(wReader.get("BOMNumber"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartCode = StringUtils.parseString(wReader.get("PartCode"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.PartPointCode = StringUtils.parseString(wReader.get("PartPointCode"));
                wItem.PartPointName = StringUtils.parseString(wReader.get("PartPointName"));
                wItem.RowNo = StringUtils.parseString(wReader.get("RowNo"));
                wItem.GroupFlag = StringUtils.parseString(wReader.get("GroupFlag"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceTypeText = StringUtils.parseString(wReader.get("ReplaceTypeText"));
                wItem.OutSourceType = StringUtils.parseInt(wReader.get("OutSourceType"));
                wItem.OutSourceTypeText = StringUtils.parseString(wReader.get("OutSourceTypeText"));
                wItem.AssessmentType = StringUtils.parseString(wReader.get("AssessmentType"));
                wItem.KittingFlag = StringUtils.parseString(wReader.get("KittingFlag"));
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.TaskStepID = StringUtils.parseInt(wReader.get("TaskStepID"));
                wItem.MaterialType = StringUtils.parseInt(wReader.get("MaterialType"));
                wItem.BOMItemNum = StringUtils.parseInt(wReader.get("BOMItemNum"));
                wItem.UnitText = StringUtils.parseString(wReader.get("UnitText"));
                wItem.PickedNumber = StringUtils.parseDouble(wReader.get("PickedNumber"));
                wItem.IsConfirmed = StringUtils.parseInt(wReader.get("IsConfirmed"));

                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.Stock = StringUtils.parseString(wReader.get("Stock"));
                wItem.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wItem.ProductName = StringUtils.parseString(wReader.get("ProductName"));
                wItem.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wItem.ProductTime = StringUtils.parseCalendar(wReader.get("ProductTime"));

                wItem.SorterID = StringUtils.parseInt(wReader.get("SorterID"));
                wItem.Sorter = StringUtils.parseString(wReader.get("Sorter"));
                wItem.SorterNo = StringUtils.parseString(wReader.get("SorterNo"));
                wItem.StockPosition = StringUtils.parseString(wReader.get("StockPosition"));
                wItem.ToHandFQTY = StringUtils.parseDouble(wReader.get("ToHandFQTY"));

                wItem.DeleteFlag = StringUtils.parseInt(wReader.get("DeleteFlag"));
                wItem.UpdateFlag = StringUtils.parseInt(wReader.get("UpdateFlag"));
                wItem.UpdateErrorMsg = StringUtils.parseString(wReader.get("UpdateErrorMsg"));
                wItem.OutSourceMaterialID = StringUtils.parseInt(wReader.get("OutSourceMaterialID"));

                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.ProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                wItem.LineName = StringUtils.parseString(wReader.get("LineName"));
                wItem.CustomerName = StringUtils.parseString(wReader.get("CustomerName"));
                wItem.DemandNo = StringUtils.parseString(wReader.get("DemandNo"));
                wItem.PartName = StringUtils.parseString(wReader.get("PartName"));

                wItem.MaterialTypeText = Objects.requireNonNull(MSSMaterialType.getEnumType(wItem.MaterialType))
                        .getLable();
                wItem.StatusText = WMSPickDemandItemStatus.getEnumType(wItem.Status).getLable();
                wItem.LoadingTime = StringUtils.parseCalendar(wReader.get("LoadingTime"));
                wItem.LoadingTimeText = wItem.LoadingTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.LoadingTime.getTime())
                        : "";

                wResult.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 且传wms取消 改为已取消，修改失败 保持完工关闭 状态 直至修改成功  改为已取消
     */
    public void CancelToWMS(BMSEmployee wLoginUser, WMSPickDemand wWMSPickDemand, WMSPickDemandItem wWMSPickDemandItem,
                            OutResult<Integer> wErrorCode) {
        try {
            OMSOrder wOMSOrder = OMSOrderDAO.getInstance().SelectByID(wLoginUser, wWMSPickDemand.OrderID, wErrorCode);

            List<WMSLinePartLL> wHeaderList = new ArrayList<>();
            WMSLinePartLL wWMSLinePartLL = GetWMSLinePartLL_Cancel(wWMSPickDemand,
                    new ArrayList<>(Collections.singletonList(wWMSPickDemandItem)));

            wHeaderList.add(wWMSLinePartLL);
            WMSLinePartLLs wWMSLinePartLLs = new WMSLinePartLLs(wHeaderList);

            WMSReturn wReturn = MyHelperServiceImpl.getInstance().WMS_SynchronizTime(wWMSLinePartLLs, wWMSPickDemand);
            if (wReturn.returnFlag.equals("1")) {
                wWMSPickDemandItem.Status = WMSPickDemandItemStatusNew.Canceled.getValue();
                wWMSPickDemandItem.UpdateFlag = 1;
                wWMSPickDemandItem.UpdateErrorMsg = "取消成功";
            } else {

                wWMSPickDemandItem.Status = WMSPickDemandItemStatusNew.FinishedClose.getValue();
                wWMSPickDemandItem.UpdateFlag = 2;

                if (StringUtils.isEmpty(wReturn.returnDesc))
                    wWMSPickDemandItem.UpdateErrorMsg = "网络错误";
                else
                    wWMSPickDemandItem.UpdateErrorMsg = "取消失败：" + wReturn.returnDesc;
            }
            this.Update(wLoginUser, wWMSPickDemandItem, wErrorCode);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 获取删除的响应报文
     */
    private WMSLinePartLL GetWMSLinePartLL_Cancel(WMSPickDemand wPickDemand, List<WMSPickDemandItem> wItemList) {
        WMSLinePartLL wResult = new WMSLinePartLL();
        try {
            wResult.warehouseId = "WH01";
            wResult.customerId = wPickDemand.Factory;
            wResult.orderType = wPickDemand.OrderType;
            wResult.docNo = wPickDemand.DemandNo;
            wResult.expectedShipmentTime1 = wPickDemand.ExpectTime1Text;
            wResult.requiredDeliveryTime = wPickDemand.ExpectTime2Text;
            wResult.consigneeId = wPickDemand.MonitorNo;
            wResult.consigneeName = wPickDemand.Monitor;
            wResult.hedi01 = wPickDemand.ProductNo;
            wResult.hedi02 = wPickDemand.LineName;
            wResult.hedi03 = wPickDemand.CustomerCode;
            wResult.hedi04 = wPickDemand.PartNo;
            wResult.hedi05 = wPickDemand.PartName;
            wResult.hedi06 = wPickDemand.PartCode;
            wResult.hedi07 = wPickDemand.WBSNo;
            wResult.hedi08 = String.valueOf(wPickDemand.OrderID);
            wResult.hedi11 = WMSPickDemandSourceType.getEnumType(wPickDemand.SourceType).getLabel();
            // wResult.hedi09 = wPickDemand.ParentDemandNo;
            wResult.details = new ArrayList<>();

            int wBOMType = 0;
            String wDeleteID = "";
            OutResult<Integer> wErrorCode = new OutResult<Integer>(0);
            for (WMSPickDemandItem wWMSPickDemandItem : wItemList) {
                if (wWMSPickDemandItem.BOMItemNum > 0) {
                    List<APSBOMItem> apsbomItems = APSBOMItemDAO.getInstance().APS_QueryBOMItemList(BaseDAO.SysAdmin,
                            -1, wPickDemand.OrderID, "",
                            "", -1, -1, -1, wWMSPickDemandItem.PartID, wWMSPickDemandItem.PartPointID,
                            wWMSPickDemandItem.MaterialID, "", -1, -1, -1, null, -1, -1, -1, wErrorCode);
                    if (apsbomItems != null && apsbomItems.size() > 0) {
                        // BOM类型
                        wBOMType = apsbomItems.get(0).BOMType;
                        // 台车BOM删除标记
                        if (apsbomItems.stream().anyMatch(p -> p.OrderNum == wWMSPickDemandItem.BOMItemNum)) {
                            wDeleteID = apsbomItems.stream().filter(p -> p.OrderNum == wWMSPickDemandItem.BOMItemNum)
                                    .findFirst().get().DeleteID;
                        }
                    }
                }

                WMSLLDetail wWMSLLDetail = new WMSLLDetail();
                wWMSLLDetail.sku = wWMSPickDemandItem.MaterialNo;
                wWMSLLDetail.qtyOrdered = "0";
                wWMSLLDetail.lotAtt08 = wPickDemand.WBSNo;
                // 截断
                if (wPickDemand.WBSNo.length() > 9) {
                    wWMSLLDetail.lotAtt08 = wPickDemand.WBSNo.substring(0, 9);
                }
                // 偶换件给空
                if (wWMSPickDemandItem.ReplaceType == 2) {
                    wWMSLLDetail.lotAtt08 = "";
                }
                wWMSLLDetail.lotAtt11 = wWMSPickDemandItem.AssessmentType;
                wWMSLLDetail.dedi01 = wWMSPickDemandItem.PartPointCode;
                wWMSLLDetail.dedi02 = wWMSPickDemandItem.PartPointName;
                wWMSLLDetail.dedi03 = wWMSPickDemandItem.RowNo;
                wWMSLLDetail.userDefine1 = wWMSPickDemandItem.GroupFlag;
                wWMSLLDetail.userDefine2 = wWMSPickDemandItem.KittingFlag;
                wWMSLLDetail.dedi11 = String.valueOf(wWMSPickDemandItem.BOMItemNum);
                wWMSLLDetail.dedi12 = String.valueOf(wBOMType);
                wWMSLLDetail.dedi13 = wDeleteID;
                wResult.details.add(wWMSLLDetail);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public WMSPickDemandItem SelectBySourceType(BMSEmployee wLoginUser, int wOrderID, int wPartID, int wPartPointID,
                                                int wSourceType, OutResult<Integer> wErrorCode) {
        WMSPickDemandItem wResult = new WMSPickDemandItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t.*,t1.Status ParentStatus FROM {0}" +
                            ".WMS_PickDemandItem t,{0}.WMS_PickDemand t1 WHERE t.DemandID=t1.ID AND t1" +
                            ".OrderID=:OrderID AND t1.SourceType=:SourceType AND t.PartID=:PartID " +
                            "AND t" +
                            ".PartPointID=:PartPointID ;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("OrderID", wOrderID);
            wParamMap.put("SourceType", wSourceType);
            wParamMap.put("PartID", wPartID);
            wParamMap.put("PartPointID", wPartPointID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.ID = StringUtils.parseInt(wReader.get("ID"));
                wResult.DemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wResult.OrderID = StringUtils.parseInt(wReader.get("Stock"));
                wResult.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wResult.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wResult.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wResult.AssessmentType = StringUtils.parseString(wReader.get("AssessmentType"));
                wResult.PartPointCode = StringUtils.parseString(wReader.get("PartPointCode"));
                wResult.PartPointName = StringUtils.parseString(wReader.get("PartPointName"));
                wResult.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wResult.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wResult.Status = StringUtils.parseInt(wReader.get("Status"));
                wResult.ParentStatus = StringUtils.parseInt(wReader.get("ParentStatus"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public WMSPickDemandItem SelectBySourceTypeAndMaterialID(BMSEmployee wLoginUser, int wOrderID, int wPartID,
                                                             int wPartPointID, int wSourceType, int materialID,
                                                             OutResult<Integer> wErrorCode) {
        WMSPickDemandItem wResult = new WMSPickDemandItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t.*,t1.Status ParentStatus FROM {0}" +
                            ".WMS_PickDemandItem t,{0}.WMS_PickDemand t1 WHERE t.DemandID=t1.ID AND t1" +
                            ".OrderID=:OrderID AND t1.SourceType=:SourceType AND t.PartID=:PartID " +
                            "AND t" +
                            ".PartPointID=:PartPointID AND t.MaterialID=:MaterialID ;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("OrderID", wOrderID);
            wParamMap.put("SourceType", wSourceType);
            wParamMap.put("PartID", wPartID);
            wParamMap.put("PartPointID", wPartPointID);
            wParamMap.put("MaterialID", materialID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.ID = StringUtils.parseInt(wReader.get("ID"));
                wResult.DemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wResult.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wResult.OrderID = StringUtils.parseInt(wReader.get("Stock"));
                wResult.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wResult.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wResult.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wResult.AssessmentType = StringUtils.parseString(wReader.get("AssessmentType"));
                wResult.PartPointCode = StringUtils.parseString(wReader.get("PartPointCode"));
                wResult.PartPointName = StringUtils.parseString(wReader.get("PartPointName"));
                wResult.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wResult.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wResult.Status = StringUtils.parseInt(wReader.get("Status"));
                wResult.ParentStatus = StringUtils.parseInt(wReader.get("ParentStatus"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<WMSPickDemandItem> WMS_QueryPickDemandItemListByCondition(BMSEmployee wLoginUser, int wProductID,
                                                                          int wLineID, int wCustomerID, int wOrderID,
                                                                          int wPartID, int wPartPointID,
                                                                          String wMaterial, String wDemandNo,
                                                                          int wUpdateFlag,
                                                                          Calendar wStartTime, Calendar wEndTime,
                                                                          Pagination wPagination,
                                                                          OutResult<Integer> wErrorCode) {
        List<WMSPickDemandItem> wResult = new ArrayList<WMSPickDemandItem>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("select t.*,t1.DemandNo,t1.ProductNo,t1.LineName,t1.CustomerName," +
                            "t1.PartNo,t1.PartName,t1.PartCode from {0}.`wms_pickdemanditem` t " +
                            "left join {0}.`wms_pickdemand` t1 on t.DemandID = t1.ID " +
                            "where 1=1 " +
                            "and (:wProductID<=0 or t1.ProductID=:wProductID) " +
                            "and (:wLineID<=0 or t1.ProductID=:wLineID) " +
                            "and (:wCustomerID<=0 or t1.CustomerID=:wCustomerID) " +
                            "and (:wOrderID<=0 or t1.OrderID=:wOrderID) " +
                            "and (:wPartID<=0 or t1.PartID=:wPartID) " +
                            "and (:wPartPointID<=0 or t.PartPointID=:wPartPointID) " +
                            "and ( :wMaterial is null or :wMaterial = '''' or t.MaterialNo like ''%{1}%'' or t" +
                            ".MaterialName like ''%{1}%''  ) " +
                            "and ( :wDemandNo is null or :wDemandNo = '''' or t1.DemandNo like ''%{2}%'' ) " +
                            "and (:wUpdateFlag<=0 or t.UpdateFlag=:wUpdateFlag) " +
                            "and ( :wStartTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wStartTime <= t1" +
                            ".CreateTime) " +
                            "and ( :wEndTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndTime >= t1" +
                            ".CreateTime) "
                    ,
                    wInstance.Result, wMaterial, wDemandNo);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wPartPointID", wPartPointID);
            wParamMap.put("wMaterial", wMaterial);
            wParamMap.put("wDemandNo", wDemandNo);
            wParamMap.put("wUpdateFlag", wUpdateFlag);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap, wPagination);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add(APSUtils.getInstance().mapToPOJO(wReader,
                        WMSPickDemandItem.class));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
            wErrorCode.set(MESException.DBSQL.getValue());
        }
        return wResult;
    }

    public List<WMSPickDemandItem> WMS_QueryPickDemandItemListByConditionNoPage(BMSEmployee wLoginUser,
                                                                                int wProductID, int wLineID,
                                                                                int wCustomerID, int wOrderID,
                                                                                int wPartID,
                                                                                int wPartPointID, String wMaterial,
                                                                                String wDemandNo, int wUpdateFlag,
                                                                                Calendar wStartTime,
                                                                                Calendar wEndTime,
                                                                                OutResult<Integer> wErrorCode) {
        List<WMSPickDemandItem> wResult = new ArrayList<WMSPickDemandItem>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("select t.*,t1.DemandNo,t1.ProductNo,t1.LineName,t1.CustomerName," +
                            "t1.PartNo,t1.PartName,t1.PartCode from {0}.`wms_pickdemanditem` t " +
                            "left join {0}.`wms_pickdemand` t1 on t.DemandID = t1.ID " +
                            "where 1=1 " +
                            "and (:wProductID<=0 or t1.ProductID=:wProductID) " +
                            "and (:wLineID<=0 or t1.ProductID=:wLineID) " +
                            "and (:wCustomerID<=0 or t1.CustomerID=:wCustomerID) " +
                            "and (:wOrderID<=0 or t1.OrderID=:wOrderID) " +
                            "and (:wPartID<=0 or t1.PartID=:wPartID) " +
                            "and (:wPartPointID<=0 or t.PartPointID=:wPartPointID) " +
                            "and ( :wMaterial is null or :wMaterial = '''' or t.MaterialNo like ''%{1}%'' or t" +
                            ".MaterialName like ''%{1}%''  ) " +
                            "and ( :wDemandNo is null or :wDemandNo = '''' or t1.DemandNo like ''%{2}%'' ) " +
                            "and (:wUpdateFlag<=0 or t.UpdateFlag=:wUpdateFlag) " +
                            "and ( :wStartTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wStartTime <= t1" +
                            ".CreateTime) " +
                            "and ( :wEndTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndTime >= t1" +
                            ".CreateTime) "
                    ,
                    wInstance.Result, wMaterial, wDemandNo);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wPartPointID", wPartPointID);
            wParamMap.put("wMaterial", wMaterial);
            wParamMap.put("wDemandNo", wDemandNo);
            wParamMap.put("wUpdateFlag", wUpdateFlag);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult.add(APSUtils.getInstance().mapToPOJO(wReader,
                        WMSPickDemandItem.class));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
            wErrorCode.set(MESException.DBSQL.getValue());
        }
        return wResult;
    }

    public int SelectIDByOrderNum(BMSEmployee wLoginUser, int orderID, int materialID, int orderNum,
                                  OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t.ID FROM {0}.wms_pickdemanditem t " +
                            "LEFT JOIN {0}.wms_pickdemand t1 ON t.DemandID=t1.ID " +
                            "WHERE t1.OrderID=:OrderID AND t.MaterialId=:MaterialId AND t.BOMItemNum=:BOMItemNum; ",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("OrderID", orderID);
            wParamMap.put("MaterialId", materialID);
            wParamMap.put("BOMItemNum", orderNum);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseInt(wReader.get("ID"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
